import sqlite3
import os
from typing import Dict, List, Tuple, Optional, Any

class DBHelper:
    def __init__(self, db_name: str):
        """初始化数据库连接"""
        self.db_name = db_name
        self.conn = None
        self.cursor = None
        self._connect()

    def _connect(self) -> None:
        """建立数据库连接"""
        try:
            self.conn = sqlite3.connect(self.db_name)
            self.conn.row_factory = sqlite3.Row  # 使查询结果可以通过列名访问
            self.cursor = self.conn.cursor()
        except sqlite3.Error as e:
            print(f"数据库连接错误: {e}")

    def close(self) -> None:
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None
            self.cursor = None

    def create_table(self, table_name: str, columns: Dict[str, str]) -> bool:
        """
        创建数据表
        :param table_name: 表名
        :param columns: 列定义，格式: {列名: 类型及约束}
        :return: 是否创建成功
        """
        try:
            columns_str = ", ".join([f"{k} {v}" for k, v in columns.items()])
            sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_str})"
            self.cursor.execute(sql)
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"创建表 {table_name} 错误: {e}")
            self.conn.rollback()
            return False

    def insert(self, table_name: str, data: Dict[str, Any]) -> Optional[int]:
        """
        插入数据
        :param table_name: 表名
        :param data: 要插入的数据，格式: {列名: 值}
        :return: 插入的记录ID，失败返回None
        """
        try:
            columns = ", ".join(data.keys())
            placeholders = ", ".join(["?" for _ in data.values()])
            sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
            self.cursor.execute(sql, tuple(data.values()))
            self.conn.commit()
            return self.cursor.lastrowid
        except sqlite3.Error as e:
            print(f"插入数据错误: {e}")
            self.conn.rollback()
            return None

    def update(self, table_name: str, data: Dict[str, Any], condition: str, params: Tuple) -> bool:
        """
        更新数据
        :param table_name: 表名
        :param data: 要更新的数据，格式: {列名: 值}
        :param condition: WHERE条件，例如: "id = ?"
        :param params: 条件参数
        :return: 是否更新成功
        """
        try:
            set_clause = ", ".join([f"{k} = ?" for k in data.keys()])
            sql = f"UPDATE {table_name} SET {set_clause} WHERE {condition}"
            values = list(data.values()) + list(params)
            self.cursor.execute(sql, tuple(values))
            self.conn.commit()
            return self.cursor.rowcount > 0
        except sqlite3.Error as e:
            print(f"更新数据错误: {e}")
            self.conn.rollback()
            return False

    def delete(self, table_name: str, condition: str, params: Tuple) -> bool:
        """
        删除数据
        :param table_name: 表名
        :param condition: WHERE条件
        :param params: 条件参数
        :return: 是否删除成功
        """
        try:
            sql = f"DELETE FROM {table_name} WHERE {condition}"
            self.cursor.execute(sql, params)
            self.conn.commit()
            return self.cursor.rowcount > 0
        except sqlite3.Error as e:
            print(f"删除数据错误: {e}")
            self.conn.rollback()
            return False

    def query(self, table_name: str, columns: str = "*", condition: str = "", 
              params: Tuple = (), order_by: str = "", limit: int = 0) -> List[Dict[str, Any]]:
        """
        查询数据
        :param table_name: 表名
        :param columns: 要查询的列
        :param condition: WHERE条件
        :param params: 条件参数
        :param order_by: 排序条件
        :param limit: 限制返回记录数
        :return: 查询结果列表
        """
        try:
            sql = f"SELECT {columns} FROM {table_name}"
            if condition:
                sql += f" WHERE {condition}"
            if order_by:
                sql += f" ORDER BY {order_by}"
            if limit > 0:
                sql += f" LIMIT {limit}"
                
            self.cursor.execute(sql, params)
            rows = self.cursor.fetchall()
            return [dict(row) for row in rows]
        except sqlite3.Error as e:
            print(f"查询数据错误: {e}")
            return []

    def query_one(self, table_name: str, columns: str = "*", condition: str = "", 
                 params: Tuple = ()) -> Optional[Dict[str, Any]]:
        """
        查询单条数据
        :param table_name: 表名
        :param columns: 要查询的列
        :param condition: WHERE条件
        :param params: 条件参数
        :return: 单条查询结果，没有找到返回None
        """
        results = self.query(table_name, columns, condition, params, limit=1)
        return results[0] if results else None

    def paginate(self, table_name: str, page: int = 1, per_page: int = 10, 
                columns: str = "*", condition: str = "", params: Tuple = (), 
                order_by: str = "") -> Dict[str, Any]:
        """
        分页查询
        :param table_name: 表名
        :param page: 页码，从1开始
        :param per_page: 每页记录数
        :param columns: 要查询的列
        :param condition: WHERE条件
        :param params: 条件参数
        :param order_by: 排序条件
        :return: 包含数据和分页信息的字典
        """
        if page < 1:
            page = 1
            
        offset = (page - 1) * per_page
        
        # 查询当前页数据
        data_sql = f"SELECT {columns} FROM {table_name}"
        if condition:
            data_sql += f" WHERE {condition}"
        if order_by:
            data_sql += f" ORDER BY {order_by}"
        data_sql += f" LIMIT {per_page} OFFSET {offset}"
        
        # 查询总记录数
        count_sql = f"SELECT COUNT(*) as total FROM {table_name}"
        if condition:
            count_sql += f" WHERE {condition}"
            
        try:
            # 获取总记录数
            self.cursor.execute(count_sql, params)
            total = self.cursor.fetchone()['total']
            
            # 获取当前页数据
            self.cursor.execute(data_sql, params)
            rows = self.cursor.fetchall()
            data = [dict(row) for row in rows]
            
            # 计算总页数
            total_pages = (total + per_page - 1) // per_page
            
            return {
                'data': data,
                'pagination': {
                    'page': page,
                    'per_page': per_page,
                    'total': total,
                    'total_pages': total_pages
                }
            }
        except sqlite3.Error as e:
            print(f"分页查询错误: {e}")
            return {'data': [], 'pagination': {'page': page, 'per_page': per_page, 'total': 0, 'total_pages': 0}}

    def execute_sql(self, sql: str, params: Tuple = ()) -> Any:
        """
        执行自定义SQL语句
        :param sql: SQL语句
        :param params: 参数
        :return: 执行结果
        """
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
            
            # 根据SQL语句类型返回不同结果
            if sql.strip().upper().startswith(('SELECT', 'PRAGMA')):
                return [dict(row) for row in self.cursor.fetchall()]
            else:
                return {
                    'rowcount': self.cursor.rowcount,
                    'lastrowid': self.cursor.lastrowid
                }
        except sqlite3.Error as e:
            print(f"执行SQL错误: {e}")
            self.conn.rollback()
            return None
